﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class CrewPayrollSQL
    {
        public static string UpdateTempPickup_flagStatus()
        {
            return @"update i_AGRESO_SYNC 
                    set 
                    AS_PICKUP_DATE_TIME=@AS_PICKUP_DATE_TIME
                    ,AS_IS_EXPORTED=@AS_IS_EXPORTED
                    , remarks=@remarks
                    , batch_id=@batch_id 
                    , pickup_date=@pickup_date
                    where 
                    AS_ID=@AS_ID and AS_ADD_DATE=@AS_ADD_DATE";
        }
        public static string InsertTempTable()
        {
            return @"
            INSERT INTO [i_AGRESO_SYNC]
           ([AS_ID]
           ,[AS_PICKUP_DATE_TIME]
           ,[AS_IS_EXPORTED]
           ,[remarks]
           ,[insert_date]
           ,[batch_id]
            ,AS_ADD_DATE
            ,[AS_ACCOUNT]
           ,[AS_VESSEL_ACC_CODE]
           ,[AS_VESSEL_CODE]
           ,[AS_MONTH]
           ,[AS_YEAR]
           ,[AS_COMPANY_CODE]
           ,[AS_CURRENCY]
           ,[AS_AMOUNT]
           ,[AS_DESCRIPTION]
           ,[AS_ADD_BY]
           ,[AS_VESSEL_NAME]
           ,[AS_COMPANY_NAME]
           ,[pickup_date]
            )
            VALUES
           (@AS_ID
           ,@AS_PICKUP_DATE_TIME
           ,@AS_IS_EXPORTED
           ,@remarks
           ,@insert_date
           ,@batch_id
            ,@AS_ADD_DATE
            ,@AS_ACCOUNT
           ,@AS_VESSEL_ACC_CODE
           ,@AS_VESSEL_CODE
           ,@AS_MONTH
           ,@AS_YEAR
           ,@AS_COMPANY_CODE
           ,@AS_CURRENCY
           ,@AS_AMOUNT
           ,@AS_DESCRIPTION
           ,@AS_ADD_BY
           ,@AS_VESSEL_NAME
           ,@AS_COMPANY_NAME
           ,@pickup_date
            )
            ";
        }

       // public static string FindByAS_ID()
       // {
       //     return @"select * from i_AGRESO_SYNC where AS_ID = @AS_ID";
       // }

        public static string FindByAS_IDAndADDDate()
        {
            return @"select * from i_AGRESO_SYNC where AS_ID = @AS_ID and AS_ADD_DATE = @AS_ADD_DATE";
        }

        public static string GetAllPendingCrewPayroll(string p)
        {
            return string.Format(@"select * from i_AGRESO_SYNC where AS_IS_EXPORTED=0
            and convert(char(11),AS_ID)+AS_PICKUP_DATE_TIME+remarks+replace(convert(char(11), AS_ADD_DATE, 106),' ','-') like '%{0}%'", p);
        }

        public static string GetAllPendingCrewPayroll()
        {
            return @"select * from i_AGRESO_SYNC where AS_IS_EXPORTED=0 ";
        }
    }
}
